package studio.raptor.ddal.dashboard.ddl;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import studio.raptor.ddal.dashboard.rule.TestHashAndModSingleKeyShardAlgorithm;
import studio.raptor.ddal.jdbc.RaptorDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * 智慧运营BSS技术架构测试用例
 *
 */
public class DdalDemo
{
    private  static Logger logger = LoggerFactory.getLogger( DdalDemo.class);
    private  DataSource dataSource=null;
    private  Connection connection;

    public void init(DataSource dataSource,Connection connection){
        this.dataSource=dataSource;
        this.connection=connection;
    }

    public void init() throws Exception{
        try {
            this.dataSource = new RaptorDataSource( "quickstart", "oracle" );
            this.connection = this.dataSource.getConnection();
        }catch(Exception e){
            throw e;
        }
    }

    /**
     * DDL语句路由
     * @param i
     * @throws Exception
     */
    public  void test4111(int i) throws Exception{
        //---------------DDAL语句路由------------------------------
        String sql0="drop table ddal_test cascade";
        String sql1="create table ddal_test(id NUMBER(10) not null, firstname varchar(255), lastname varchar(255))";
        String sql2="alter table ddal_test add(age number(3))";
        String sql3="alter table ddal_test rename column age to state";
        String sql4="alter table ddal_test drop column state";
        String sql5="create index ddal_test_firstname on ddal_test(firstname)";
        String sql6="drop index ddal_test_firstname";
        PreparedStatement preparedStatement=null;
        preparedStatement = connection.prepareStatement(sql0);
        if(i==1){
            preparedStatement = connection.prepareStatement(sql1);
        }
        if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
        }
        else if(i==5){
            preparedStatement = connection.prepareStatement(sql5);
        }
        else if(i==6){
            preparedStatement = connection.prepareStatement(sql6);
        }
        try{
            preparedStatement.execute();
            connection.commit();
        }catch(Exception e){
            throw e;
        }
    }

    /**
     * DML语句路由
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test4112(int i,long id) throws Exception{
        //---------------DDAL语句路由------------------------------
        String sql1="insert into ddal_test(id,firstname,lastname) values(?,'测试',?)";
        String sql2="update ddal_test set firstname='测试4112' where id=?";
        String sql3="delete from ddal_test where id=?";
        String sql4="select * from ddal_test where id = ?";
        PreparedStatement preparedStatement=null;
        if(i==1){
            for(int j=0;j<8;j++){
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setLong(1,id);
                preparedStatement.setString( 2,"testDdal_"+id);
                preparedStatement.execute();
                id++;
            }
            connection.commit();
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
            }
        }
    }

    /**
     * 单字段路由
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test4113(int i,long id) throws Exception{
        //---------------DDAL语句路由------------------------------
        String sql1 = "select * from ddal_test where id = ?";
        String sql2 = "select * from ddal_test where firstname='测试'";
        String sql3 = "select * from ddal_test where id> ?";
        String sql4 = "select * from ddal_test where id between 2 and 5";
        String sql5 = "select * from ddal_test where id in(3,5,8)";
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        if(i==1){
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setLong( 1,id );
            resultSet=preparedStatement.executeQuery();
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            resultSet=preparedStatement.executeQuery();
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setLong( 1,id );
            resultSet=preparedStatement.executeQuery();
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
            resultSet=preparedStatement.executeQuery();
        }
        else if(i==5){
            preparedStatement = connection.prepareStatement(sql5);
            resultSet=preparedStatement.executeQuery();
        }
        if(resultSet!=null){
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
            }
        }
    }

    /**
     * 单字段语句路由 简单性能测试：多分片执行时间小于N*300ms
     * @param i
     * @param id
     * @throws Exception
     */
    public  long test4113WithTime(int i,long id) throws Exception
    {
        long start = System.currentTimeMillis();
        for(int j=0; j< 100; j++){
            test4113( i,id );
        }
        long time=(System.currentTimeMillis() - start)/100;
        System.out.println(time+ "ms");
        return time;
    }

    /**
     * 全局表操作：查询与修改
     * @throws Exception
     */
    public  void test4114() throws Exception{
        //查询
        String sql="Select * from ddal_quanju";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next())
        {
            System.out.println("id:" + resultSet.getLong(1));
            System.out.println("name:" + resultSet.getString(2));
            System.out.println("taocan:" + resultSet.getString(4));
        }
        //修改
        sql = "UPDATE ddal_quanju SET TAOCAN = 'A套餐-1' where id = 1";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.execute();
        connection.commit();
        //查询
        sql="Select * from ddal_quanju where id=1";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next())
        {
            System.out.println("id:" + resultSet.getLong(1));
            System.out.println("name:" + resultSet.getString(2));
            System.out.println("taocan:" + resultSet.getString(4));
        }
    }

    /**
     * 全局表操作：与分区表关联查询，全局表和分片表关联，按分片表路由规则进行路由
     * @throws Exception
     */
    public  void test4115() throws Exception{
        //查询
        String sql="Select a.* from ddal_quanju a,ddal_test b where b.id=5 and a.id=b.id";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next())
        {
            System.out.println("id:" + resultSet.getLong(1));
            System.out.println("name:" + resultSet.getString(2));
            System.out.println("taocan:" + resultSet.getString(4));
        }
        //修改
        sql = "UPDATE ddal_quanju SET TAOCAN = 'A套餐-2' where id = 5";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.execute();
        connection.commit();
        //查询
        sql="Select a.* from ddal_quanju a,ddal_test b where b.id=5 and a.id=b.id";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next())
        {
            System.out.println("id:" + resultSet.getLong(1));
            System.out.println("name:" + resultSet.getString(2));
            System.out.println("taocan:" + resultSet.getString(4));
        }
    }

    /**
     * 根据ID/4 取模分片策略：DefaultModSingleKeyAlgorithm
     * 水平分表，可以把一个逻辑表切分到不同的数据节点
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test4121(int i,long id) throws Exception{
        //---------------DDAL语句路由------------------------------
        String sql1="insert into ddal_testMod(id,firstname,lastname) values(?,'测试-取模方式路由',?)";
        String sql2="update ddal_testMod set firstname='测试-取模方式路由4121' where id=?";
        String sql3="delete from ddal_testMod where id=?";
        String sql4="select * from ddal_testMod where id = ?";
        String sql5="/*!hint readonly */select * from ddal_testMod where id = ?";
        PreparedStatement preparedStatement=null;
        if(i==1){
            for(int j=0;j<8;j++){
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setLong(1,id);
                preparedStatement.setString( 2,"testDdal_Mod_"+id);
                preparedStatement.execute();
                id++;
            }
            connection.commit();
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
            }
        }
        else if(i==5){
            preparedStatement = connection.prepareStatement(sql5);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
            }
        }
    }

    /**
     * 根据ID/4 取模分片策略：DefaultModSingleKeyAlgorithm
     * 水平分表，可以把一个逻辑表切分到不同的数据节点
     * @throws Exception
     */
    public  void test4121() throws Exception{
/*        //insert into ddal_test(id,firstname,lastname)
        //id=10....18
        ddalDemo.test4112( 1,10 );
        //select * from ddal_test where id = ?
        ddalDemo.test4112( 4,10 );
        ddalDemo.test4112( 4,16 );*/

        //insert into ddal_testMod(id,firstname,lastname)
        //id=10....18
        test4121( 1,10 );
        //select * from ddal_testMod where id = ?
        test4121( 4,10 );
        test4121( 4,16 );
    }

    /**
     * 键值对方式路由测试，离散地指定分片字段值和节点下标的对应，DML操作
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test4122(int i,long id) throws Exception{
        //---------------DDAL语句路由------------------------------
        String sql1="insert into ddal_testKeyValue(id,firstname,lastname,type) values(?,'测试-键值对方式路由',?,?)";
        String sql2="update ddal_testKeyValue set firstname='测试-键值对方式路由4122' where type=?";
        String sql3="delete from ddal_testKeyValue where type=?";
        String sql4="select * from ddal_testKeyValue where type = ?";
        String sql5="select * from ddal_testKeyValue where type = ? and id=?";
        String sql6="/*!hint readonly */ select * from ddal_testKeyValue where type = ? and id=?";
        PreparedStatement preparedStatement=null;
        long type=1;
        if(i==1){
            for(int j=0;j<8;j++){
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setLong(1,id);
                preparedStatement.setString( 2,"testDdal_KeyValue_"+id);
                type=id % 4;
                preparedStatement.setLong( 3,type );
                preparedStatement.execute();
                id++;
            }
            connection.commit();
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
                System.out.println("type:" + resultSet.getString(4));
            }
        }
        else if(i==5){
            preparedStatement = connection.prepareStatement(sql5);
            type=id % 4;
            preparedStatement.setLong( 1,type );
            preparedStatement.setLong( 2,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
                System.out.println("type:" + resultSet.getString(4));
            }
        }
        else if(i==6){
            preparedStatement = connection.prepareStatement(sql6);
            type=id % 4;
            preparedStatement.setLong( 1,type );
            preparedStatement.setLong( 2,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
                System.out.println("type:" + resultSet.getString(4));
            }
        }
    }

    /**
     * 数字型字段值按区间路由，DML操作
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test4123(int i,long id) throws Exception{
        //---------------DDAL语句路由------------------------------
        String sql1="insert into ddal_testRange(id,firstname,lastname,state) values(?,'测试-按区间路由',?,?)";
        String sql2="update ddal_testRange set firstname='测试-按区间路由4123' where state=?";
        String sql3="delete from ddal_testRange where state=?";
        String sql4="select * from ddal_testRange where state = ?";
        PreparedStatement preparedStatement=null;
        long type=1;
        long state=0;
        if(i==1){
            for(int j=0;j<8;j++){
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setLong(1,id);
                preparedStatement.setString( 2,"testDdal_Range_"+id);
                type=id % 4;
                state=type*100+id;
                preparedStatement.setLong( 3,state );
                preparedStatement.execute();
                id++;
            }
            connection.commit();
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
                System.out.println("state:" + resultSet.getString(4));
            }
        }
    }

    /**
     * 字符型字段hash后再取模所得值按区间路由，DML操作
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test4124(int i,long id) throws Exception{
        //---------------DDAL语句路由------------------------------
        String sql1="insert into ddal_testHash(id,firstname,lastname,hash,hashmod) values(?,'测试-字段hash后再取模路由',?,?,?)";
        String sql2="update ddal_testHash set firstname='测试-字段hash后再取模路由4124' where lastname=?";
        String sql3="delete from ddal_testHash where lastname=?";
        String sql4="select * from ddal_testHash where lastname = ?";
        PreparedStatement preparedStatement=null;
        long hash=1;
        if(i==1){
            for(int j=0;j<8;j++){
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setLong(1,id);
                String lastName="testDdal_Hash_"+id;
                preparedStatement.setString( 2,lastName);
                hash= TestHashAndModSingleKeyShardAlgorithm.hash(lastName );
                preparedStatement.setLong( 3,hash );
                preparedStatement.setLong( 4,hash%4 );
                preparedStatement.execute();
                id++;
            }
            connection.commit();
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            String lastName="testDdal_Hash_"+id;
            preparedStatement.setString( 1,lastName );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            String lastName="testDdal_Hash_"+id;
            preparedStatement.setString( 1,lastName );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
            String lastName="testDdal_Hash_"+id;
            preparedStatement.setString( 1,lastName );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
                System.out.println("hash:" + resultSet.getString(4));
                System.out.println("hashmod:" + resultSet.getString(5));
            }
        }
    }

    /**
     * 分片算法：分片字段为数字型，获取最后一位并取模
     * 可以扩展的函数路由:，DML操作
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test4125(int i,long id) throws Exception{
        //---------------DDAL语句路由------------------------------
        String sql1="insert into ddal_testOther(id,firstname,lastname) values(?,'测试-可扩展函数路由',?)";
        String sql2="update ddal_testOther set firstname='测试-可扩展函数路由4125' where id=?";
        String sql3="delete from ddal_testOther where id=?";
        String sql4="select * from ddal_testOther where id = ?";
        PreparedStatement preparedStatement=null;
        if(i==1){
            for(int j=0;j<8;j++){
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setLong(1,id);
                preparedStatement.setString( 2,"testDdal_Other_"+id);
                preparedStatement.execute();
                id++;
            }
            connection.commit();
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
            }
        }
    }

    /**
     * 跨分片结果集合并，各节点结果再分组合并:count,group by,order by,排序并分页
     * @param i
     * @throws Exception
     */
    public  void test4131(int i) throws Exception{
        String sql1 = "select count(*) from ddal_test";
        String sql2 = "select firstname,count(*) from ddal_test group by firstname";
        String sql3 = "select firstname,count(*) as total from ddal_test group by firstname order by total desc";
        String sql4 = "/*!hint page(offset=0,count=1)*/select firstname,count(*) as total from ddal_test group by firstname order by total desc";
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        if(i==1){
            preparedStatement = connection.prepareStatement(sql1);
            resultSet = preparedStatement.executeQuery();
            while (resultSet!=null && resultSet.next())
            {
                System.out.println("总数:" + resultSet.getLong(1));
            }
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("firstname:" + resultSet.getString(1));
                System.out.println("总数:" + resultSet.getLong(2));
            }
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("firstname:" + resultSet.getString(1));
                System.out.println("总数:" + resultSet.getLong(2));
            }
        }
        else if(i==4){
            preparedStatement = connection.prepareStatement(sql4);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("firstname:" + resultSet.getString(1));
                System.out.println("总数:" + resultSet.getLong(2));
            }
        }
    }

    /**
     * 跨分片结果集合并，简单性能测试
     * @param i
     * @throws Exception
     */
    public  long test4131WithTime(int i) throws Exception{
        long start = System.currentTimeMillis();
        for(int j=0; j< 100; j++){
            test4131( i );
        }
        long time=(System.currentTimeMillis() - start)/100;
        System.out.println(time+ "ms");
        return time;
    }

    /**
     * 批量刷数据，支持批量快速进行数据更新，批量刷数据性能<60S
     * @param name
     * @param newName
     * @throws Exception
     */
    public  void test4133(String name,String newName) throws Exception{
        Date start_time = new Date();
        //准备总数据量2000W，其中至少100W条需要刷新的数据
        String sql="update ddal_shuju set name=? where NAME = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString( 1,newName );
        preparedStatement.setString( 2,name );
        preparedStatement.execute();
        connection.commit();
        Date end_time = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SSS");
        Long run_time = end_time.getTime() - start_time.getTime();
        String start_times=sdf.format(start_time);
        String end_times=sdf.format(end_time);
        System.out.println("  start_time:" +start_times+"  end_time: "+end_times+"  run_time:"+run_time);
    }

    /**
     * 数据库扩容 与 伸缩
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test42(int i,long id) throws Exception{
        //插入
        String sql1="insert into teacher(tno,tname,sex,age,tphone) VALUES (?,'testDdal','男',26，18045678901)";
        String sql2="Select * from teacher WHERE tno =?";
        String sql3="/*!hint page(offset=0,count=1)*/Select * from teacher WHERE tno =?";
        String sql4="/*!hint readonly */Select * from teacher WHERE tno =?";
        if(i==1){
            PreparedStatement preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setLong( 1,id );
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==2){
            PreparedStatement preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("tno:" + resultSet.getLong(1));
                System.out.println("tname:" + resultSet.getString(2));
                System.out.println("sex:" + resultSet.getString(3));
                System.out.println("age:" + resultSet.getLong(4));
            }
        }
        else if(i==3){
            PreparedStatement preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("tno:" + resultSet.getLong(1));
                System.out.println("tname:" + resultSet.getString(2));
                System.out.println("sex:" + resultSet.getString(3));
                System.out.println("age:" + resultSet.getLong(4));
            }
        }
        else if(i==4){
            PreparedStatement preparedStatement = connection.prepareStatement(sql4);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("tno:" + resultSet.getLong(1));
                System.out.println("tname:" + resultSet.getString(2));
                System.out.println("sex:" + resultSet.getString(3));
                System.out.println("age:" + resultSet.getLong(4));
            }
        }
    }

    /**
     * 数据读写分离  !hint readonly  语法
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test431(int i,long id) throws Exception{
        //插入
        String sql1="insert into ddal_testMod(id,firstname,lastname) values(?,'测试-数据读写分离',?)";
        String sql2="/*!hint readonly */ select a.* from ddal_testMod a where  a.id=?";
        if(i==1){
            PreparedStatement preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setLong(1,id);
            preparedStatement.setString( 2,"测试-数据读写分离"+id);
            preparedStatement.execute();
            connection.commit();
        }
        else if(i==2){
            PreparedStatement preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("id:" + resultSet.getLong(1));
                System.out.println("firstname:" + resultSet.getString(2));
                System.out.println("lastname:" + resultSet.getString(3));
            }
        }
    }

    /**
     * 主备库切换后，批量写入数据，验证底层数据同步机制
     * @param begin
     * @param max
     * @return
     * @throws Exception
     */
    public  long test432WithTime(int begin,int max) throws Exception{
        long start = System.currentTimeMillis();
        for(; begin<= max; begin++){
            test432(begin );
        }
        long end=System.currentTimeMillis();
        System.out.print("endTime:"+end);
        Date endDate=new Date(end);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm:ss:SSSSSS");
        String endDateStr=sdf.format(endDate);
        System.out.println("   "+endDateStr);

        long time=(end - start)/100;
        System.out.println(time+ "ms");
        return time;
    }

    /**
     * 单记录插入 insert into ddal_testKeyValue
     * @param id
     * @throws Exception
     */
    public  void test432(long id) throws Exception{
        String sql1="insert into ddal_testKeyValue(id,firstname,lastname,type) values(?,'测试-批量写入数据','testDdal',?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql1);
        preparedStatement.setLong(1,id);
        long type=id % 4;
        preparedStatement.setLong( 2,type );
        preparedStatement.execute();
        connection.commit();
    }

    /**
     * 批量写入数据性能测试
     * @param begin
     * @param max
     * @return
     * @throws Exception
     */
    public long testBatchInsert(int begin,int max,int num) throws Exception{
        long start = System.currentTimeMillis();
        String sql1="insert into ddal_testKeyValue(id,firstname,lastname,type) values(?,'测试-批量写入数据','testDdal',?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql1);
        for(; begin< max; begin++){
            preparedStatement.setLong(1,begin);
            long type=begin % 4;
            preparedStatement.setLong( 2,type );
            preparedStatement.addBatch();
            if( begin % num ==0){
                preparedStatement.executeBatch();
                connection.commit();
                preparedStatement = connection.prepareStatement(sql1);
            }
        }
        preparedStatement.executeBatch();
        connection.commit();
        long end=System.currentTimeMillis();
        System.out.print("endTime:"+end);
        Date endDate=new Date(end);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm:ss:SSSSSS");
        String endDateStr=sdf.format(endDate);
        System.out.println("   "+endDateStr);

        long time=(end - start)/100;
        System.out.println(time+ "ms");
        return time;
    }

    /**
     * 批量写入数据性能测试
     * @param begin
     * @param max
     * @return
     * @throws Exception
     */
    public  long testBatchInsertCustomer(int begin,int max,int num) throws Exception{
        long start = System.currentTimeMillis();
        String sql1="INSERT INTO  customer(id, name, create_time, state,gender)  VALUES(?,?,?,'A',?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql1);
        for(; begin< max; begin++){
            //添加预定义参数 
            java.sql.Date createTime=new java.sql.Date( System.currentTimeMillis() );
            preparedStatement.setLong(1,begin);
            preparedStatement.setString( 2,"testCustomer"+begin );
            preparedStatement.setDate( 3, createTime);
            preparedStatement.setLong( 4,begin );
            preparedStatement.addBatch();
            if( begin % num ==0){
                preparedStatement.executeBatch();
                connection.commit();
                preparedStatement = connection.prepareStatement(sql1);
            }
        }
        preparedStatement.executeBatch();
        connection.commit();
        long end=System.currentTimeMillis();
        System.out.print("endTime:"+end);
        Date endDate=new Date(end);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm:ss:SSSSSS");
        String endDateStr=sdf.format(endDate);
        System.out.println("   "+endDateStr);

        long time=(end - start)/100;
        System.out.println(time+ "ms");
        return time;
    }

    /**
     * 全局序列测试
     * @param i
     * @param id
     * @throws Exception
     */
    public  void test441(int i,long id) throws Exception{
        //启动zkServer和zkCli..配置sysrtem.properties
        String sql1="INSERT into course(CNO,CNAME) VALUES (?,'guchen')";
        String sql2="select a.* from course a where  a.CNO=?";
        String sql3="/*!hint readonly */ select a.* from course a where  a.CNO=?";
        PreparedStatement preparedStatement=null;
        if(i==1){
            for(int j=1;j<=8;j++){
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setLong( 1,id );
                preparedStatement.executeUpdate();
                id++;
            }
            connection.commit();
        }
        else if(i==2){
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("CNO:" + resultSet.getLong(1));
                System.out.println("CNAME:" + resultSet.getString(2));
                System.out.println("TNO:" + resultSet.getString(3));
            }
        }
        else if(i==3){
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setLong( 1,id );
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next())
            {
                System.out.println("CNO:" + resultSet.getLong(1));
                System.out.println("CNAME:" + resultSet.getString(2));
                System.out.println("TNO:" + resultSet.getString(3));
            }
        }
    }

    public  void test442(long id1,long id2) throws Exception{
        String sql="update course set cname='Geology444' where cno=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setLong( 1,id1 );
        preparedStatement.executeUpdate();
        connection.commit();

        sql="update course set cname='Geology444' where cno=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setLong( 1,id2 );
        preparedStatement.executeUpdate();
//        connection.commit();
    }

    public  static void main( String[] args ) throws SQLException{
        try {
            DdalDemo ddalDemo=new DdalDemo();
            ddalDemo.init();

            //drop table ddal_test cascade
            //先删除表
//            ddalDemo.test4111( 0 );
            //create table ddal_test
//            ddalDemo.test4111( 1 );
            //alter table ddal_test add(age number(3))
//            ddalDemo.test4111( 2 );
            //create index ddal_test_firstname on ddal_test(firstname)
//            ddalDemo.test4111( 5 );
            //alter table ddal_test rename column age to state
//            ddalDemo.test4111( 3 );
            //alter table ddal_test drop column state
//            ddalDemo.test4111( 4 );
            //drop index ddal_test_firstname报错
//            ddalDemo.test4111( 6 );


            //insert into ddal_test(id,firstname,lastname)
            //id=1....8
//            ddalDemo.test4112( 1,1 );
            //update ddal_test
//            ddalDemo.test4112( 2,1 );
            //delete from ddal_test where id=?
//            ddalDemo.test4112( 3,1 );
            //select * from ddal_test where id = ?
//            ddalDemo.test4112( 4,1 );
//            ddalDemo.test4112( 4,3 );


            //select * from ddal_test where id = 1
//            ddalDemo.test4113( 1,5 );
            //select * from ddal_test where firstname='测试'
//            ddalDemo.test4113( 2,1 );
            //select * from ddal_test where id>6
//            ddalDemo.test4113( 3,5 );
            //select * from ddal_test where id between 2 and 5
//            ddalDemo.test4113( 4,1 );
            //select * from ddal_test where id in(3,5,8)
//            ddalDemo.test4113( 5,1 );
            //单字段语句路由 简单性能测试：多分片执行时间小于N*300ms
//            ddalDemo.test4113WithTime( 1,5 );


            //验证全局表的插入、更新操作会实时在所有节点上执行
//            ddalDemo.test4114( );


            //验证全局表和分片表关联，按分片表路由规则进行路由。
//            ddalDemo.test4115( );


            //水平分表，可以把一个逻辑表切分到不同的数据节点
//            ddalDemo.test4121();
            //insert into ddal_testMod(id,firstname,lastname)
            //id=1....8
//            ddalDemo.test4121( 1,1 );
            //update ddal_testMod
//            ddalDemo.test4121( 2,1 );
            //select * from ddal_testMod where id = ?
//            ddalDemo.test4121( 4,1 );
            //delete from ddal_testMod where id=?
//            ddalDemo.test4121( 3,1 );
            //select * from ddal_testMod where id = ?
//            ddalDemo.test4121( 4,5 );

            //键值对方式，离散地指定分片字段值和节点下标的对应
            //insert into ddal_testKeyValue(id,firstname,lastname)
            //id=1....8
//            ddalDemo.test4122( 1,1 );
            //update ddal_testKeyValue
//            ddalDemo.test4122( 2,1 );
            //select * from ddal_testKeyValue where type = ?
//            ddalDemo.test4122( 4,1 );
            //delete from ddal_testKeyValue where type=?
//            ddalDemo.test4122( 3,1 );
            //select * from ddal_testKeyValue where type = ?
//            ddalDemo.test4122( 4,1 );

            //数字型字段值按区间路由
            //insert into ddal_testRange(id,firstname,lastname)
            //id=1....8
//            ddalDemo.test4123( 1,1 );
            //update ddal_testRange
//            ddalDemo.test4123( 2,1 );
            //select * from ddal_testRange where state = ?
//            ddalDemo.test4123( 4,4 );
            //delete from ddal_testRange where state=?
//            ddalDemo.test4123( 3,4 );
            //select * from ddal_testRange where state = ?
//            ddalDemo.test4123( 4,4 );

            //字符型字段hash后再取模路由 hash ,hashmod 自动生成
            //insert into ddal_testHash(id,firstname,lastname)
            //id=1....8
//            ddalDemo.test4124( 1,10 );

            //update ddal_testHash
//            ddalDemo.test4124( 2,1 );
            //select * from ddal_testHash where lastname = ?
//            ddalDemo.test4124( 4,1 );
            //delete from ddal_testHash where lastname=?
//            ddalDemo.test4124( 3,1 );
            //select * from ddal_testHash where lastname = ?
//            ddalDemo.test4124( 4,1 );

            //字符型字段hash后再取模路由
            //insert into ddal_testOther(id,firstname,lastname)
            //id=1....8
//            ddalDemo.test4125( 1,1 );
//            ddalDemo.test4125( 1,50 );
            //update ddal_testOther
//            ddalDemo.test4125( 2,1 );
            //select * from ddal_testOther where id = ?
//            ddalDemo.test4125( 4,1 );
            //delete from ddal_testOther where id=?
//            ddalDemo.test4125( 3,1 );
            //select * from ddal_testOther where id = ?
//            ddalDemo.test4125( 4,1 );

            //跨分片结果集合并，各节点结果再分组合并:count,group by,order by,排序并分页
            //select count(*) from ddal_test
//            ddalDemo.test4131( 1 );
//            ddalDemo.test4131( 2 );
//            ddalDemo.test4131( 3 );
            //通过注解实现分页,不能通过rownum实现分页
//            ddalDemo.test4131( 4 );
            //跨分片结果集合并,简单测试性能
//            ddalDemo.test4131WithTime( 2 );

            //4.1.3.2 跨分片结果集合并，排序各节点结果再排序合并
            //需要 登陆CRM应用 进行测试，类似 4.1.3.1


            //批量刷数据，支持批量快速进行数据更新，批量刷数据性能<60S
            //update ddal_shuju
            //guchen  testDdal  liushuya 测试
            String newName="更新";
            String name="liushuya";
            ddalDemo.test4133( name,newName );


            //数据库扩容 与 伸缩
            //insert into teacher(tno,tname,sex,age,tphone)
//            ddalDemo.test42( 1,1500051 );
//            ddalDemo.test42( 1,1500050 );
//            ddalDemo.test42( 1,2500050 );
//            ddalDemo.test42( 1,3500050 );
            //Select * from teacher WHERE tno =
//            ddalDemo.test42( 2,1500051 );
            ///*!hint readonly */Select * from teacher WHERE tno =?
//            ddalDemo.test42( 4,3500050 );
            /*
            long start = System.currentTimeMillis();
            for(int i=0;i<1000;i++){
                ddalDemo.test42( 1,i );
            }
            long time=(System.currentTimeMillis() - start)/100;
            System.out.println(time+ "ms");*/


            //数据读写分离
            //insert into ddal_testMod
//            ddalDemo.test431( 1,60 );
            // /*!hint readonly */ select a.* from ddal_testMod a where  a.id=?
//            ddalDemo.test431( 2,60 );
            ///*!hint readonly */select * from ddal_testMod where id = ?
//            ddalDemo.test4121( 5,60 );


            //写库、读库/主备库切换后，批量写数据验证 底层数据同步机制
            //insert into ddal_testKeyValue
//            ddalDemo.test432WithTime( 1,10000 );
            //select * from ddal_testKeyValue where type = ?
//            ddalDemo.test4122( 5,9000 );
//            ddalDemo.test4122( 5,10021 );
//            ddalDemo.test4122( 5,10030 );
//            ddalDemo.test4122( 5,10031 );
            ///*!hint readonly */ select * from ddal_testKeyValue
//            ddalDemo.test4122( 6,9000 );
//            ddalDemo.test4122( 6,10021 );
//            ddalDemo.test4122( 6,10030 );
//            ddalDemo.test4122( 6,10031 );

//            ddalDemo.testBatchInsert( 10001,20000,10000 );

//             ddalDemo.testBatchInsertCustomer( 201,20000,5000 );


            //全局序列测试  course breadcrumb方式: auto_key_seq_course tno
//            ddalDemo.test441( 1,25 );
//            ddalDemo.test441( 1,58 );
//            ddalDemo.test441( 2,30 );
//            ddalDemo.test441( 3,32 );

            //事物控制 course
//            long id1=4;
//            long id2=8;
//            ddalDemo.test442( id1,id2 );


        }catch (Exception e){
            e.printStackTrace();
        }


    }

}
